Found 93 acfrs that are in the top 100 largest of NCES.
SD in the top 100 NCES that are not collected in the current ACFRs database
NYC DOE reports total numbers of 32 school districts. https://infohub.nyced.org/docs/default-source/default-document-library/2021-annual-financial-statements.pdf Page 26, 33
Method: Apportion Acfrs data for School Districts - Calculate the students share of each school district. - Multiply this share to the DOE total.
# input number from DOE report
nyc_2020 <- nces %>% filter(str_detect(nces_original_name, "(?i)NEW YORK CITY GEOGRAPHIC DISTRICT")) %>%
#year 2020 - Department of Education NYC
mutate(year = 2020,
doe_total_liabilities = 59391179000, #page 33
doe_net_opeb = 35457858000, #page 33
doe_net_pension = 0, #(Geoff checked, no net pension)
doe_expenses = 30782114000,# page 26
doe_total_asset = 66355469000, #page 33
doe_revenues = 28104219000) #page 26
nyc_2021 <- nces %>% filter(str_detect(nces_original_name, "(?i)NEW YORK CITY GEOGRAPHIC DISTRICT")) %>%
#year 2021 - Department of Education NYC
mutate(year = 2021,
doe_total_liabilities = 48619741000, #page 33
doe_net_opeb = 38982221000,#page 33
doe_net_pension = -1137314, # page 33 (Geoff: adding - due to over funding, other than this, prob only state Wisconsin)
doe_expenses = 29278093000,# page 26
doe_total_asset = 66152289000, #page 33
doe_revenues = 28976372000) #page 26
nyc_20_21 <- rbind(nyc_2020, nyc_2021)
nyc_20_21 %>%
# students share
mutate(
id = NA,
share_student = students/sum(students),
# apportion financial data for all 32 sd
total_liabilities = doe_total_liabilities*share_student,
net_opeb_liability = doe_net_opeb*share_student,
net_pension_liability = doe_net_pension*share_student,
expenses = doe_expenses*share_student,
revenues = doe_revenues*share_student) %>%
#
select(ncesID, nces_original_name, year, county_name, city, state, id, students, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues) %>%
# only get 5 of those who are in top100 largest
filter(ncesID %in% top_sd_acfrs_10$ncesID) -> nyc_20_21_5sd
It looks like Boston Public Schools finances are consolidated with the city. They city’s FY21 ACFR is here: ACFR_01.31.22_FINAL.pdf (boston.gov). It does not provide discreet reporting for schools, so we’ll have to make some assumptions.
Total FY21 expenses were $2,083,935,000 and program revenues total to $568,994,000 (p. 20). It lists FTE employees at 10,149 (9,528+621) in FY21 (p. 150). That’s 53.82% of total city FTEs.
Citywide pension liability is $1,406,402,000 and OPEB is $2,196,724,000. If we apply that ratio, the calculated pension liability is $756,926,000 and OPEB is $1,182,277,000. We can’t calculate anything else.
fte_employee_share = .5382 #FTE employees at 10,149 (9,528+621) in FY21 (p. 150)
boston_city_2021 <- readRDS("data/data_from_dbsite_2021.RDS") %>%
filter(category == "General Purpose" & name == "Boston" & state == "MA") %>%
mutate(id = as.character(id)) %>%
select(state, name,id, year, total_liabilities, net_pension_liability, net_opeb_liability, expenses, revenues)
boston_2021 <- nces %>% filter(nces_original_name == "Boston" & state == "MA") %>%
mutate(
id = NA,
year = 2021,
# apportion sd from city
total_liabilities = 0, # can't do this city_total_liability * fte_employee_share
net_opeb_liability = boston_city_2021$net_opeb_liability*fte_employee_share,
net_pension_liability = boston_city_2021$net_pension_liability*fte_employee_share,
# manual insert
expenses = 2083935000, #schools line, page 20
revenues = 539271000 + 18727000 + 10996000) %>% # sum of 3 cols program revenues, school line, p20, school district run by the city
#
select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)
##### 2020
fte_employee_share = .5382 #10149
boston_city_2020 <- readRDS("data/data_from_dbsite_2020.RDS") %>%
filter(category == "General Purpose" & name == "Boston" & state == "MA") %>%
mutate(id = as.character(id)) %>%
select(state, name,id, year, total_liabilities, net_pension_liability, net_opeb_liability, expenses, revenues)
boston_2020 <- nces %>% filter(nces_original_name == "Boston" & state == "MA") %>%
mutate(
id = NA,
year = 2020,
# apportion sd from city
total_liabilities = 0, # can't do this: city_total_liability * fte_employee_share
net_opeb_liability = boston_city_2020$net_opeb_liability*fte_employee_share,
net_pension_liability = boston_city_2020$net_pension_liability*fte_employee_share,
# manual insert
expenses = 1874077000, #schools line, page A-17, school line
revenues = 9444000 + 60527000 + 10200000) %>% ## sum of 3 cols program revenues, school line, page A-17
#
select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)
Chesterfield County schools are consolidated with the county, but reported as a component unit: 762 (chesterfield.gov). - Expenses and revenues can be found on p. 227. - Balance sheet items are on p. 224. - Net pension liability is broken out on p. 169.
chesterfield_2021 <- nces %>% filter(nces_original_name == "CHESTERFIELD CO PBLC SCHS" & state == "VA") %>%
mutate(
id = NA,
total_assets = 122955915, #page 224
year = 2021,
total_liabilities = 62869374,#page 224
net_opeb_liability = 0,## How about page 179, Schedule of changes in Net OPEB?? -> have both 2020, 2021
net_pension_liability = 0, #Geoff: should use the net pension liability on p. 169. That seems to focus on teachers.
#OR: should be this number???
#net_pension_liability = 75809615, - page 174
expenses = 709920047, # #Geoff: For revenues and expenses, use the values on p. 225; those on p. 227 are just a subcomponent.
revenues = 704737890) %>%
#
select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)
#######
chesterfield_2020 <- nces %>% filter(nces_original_name == "CHESTERFIELD CO PBLC SCHS" & state == "VA") %>%
mutate(
id = NA,
total_assets = 121514512, # page 224, Schedule C-1, Discretely presented Component Unit- School board
year = 2020,
total_liabilities = 63252560, # page 224,Schedule C-1, Discretely presented Component Unit- School board
net_opeb_liability = 0,
net_pension_liability = 1223857, #page 169, school board component unit, column 2020, Chesterfield ACFRS 2021 OR should be page 174: Program total pension liability ending 2020: 76,292,808 & 2021: 74,809,615
expenses = 659875816, # page 225, column School operating, Schedule C-2, Discretely presented Component Unit- School board - Statement of Revenues, Expenditure and changes in Fund Balance
revenues = 658178834) %>% # page 225,
#
select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)
Nashville schools are consolidated with Davidson County: ACFRFY21_01_21_2022_Upload.pdf (nashville.gov). Balance sheet items are on p. B-6 and should be the sum of columns called “General Purpose School,” “Education Services,” and “GSD School Purposes Debt Service.” Revenues and expenditures are on p. B-10 and includes the same columns.
–> Page B-143 shows “school professional employees’
employee_share = .4848
county_opep_liabilities_2021 = 3240451063 # p. B-142.
county_opep_liabilities_2020 = 3064106607 # p. B-142.
davidson_county_2021 <- nces %>% filter(nces_original_name == "Davidson County" & state == "TN") %>%
mutate(
id = NA,
total_assets = 649639619 + 118474605 + 124590923, # sum of columns called "General Purpose School," "Education Services," and "GSD School Purposes Debt Service."
year = 2021,
total_liabilities = 44660790 + 89820946 + 212989, # sum of above 3 columns
net_opeb_liability = county_opep_liabilities_2021*employee_share,
#OR use page B-143 school professional employees
#net_opeb_liability = 1061732808,
net_pension_liability = 0,
expenses = 758516604 + 363357551 + 108450334, #B-10
revenues = 982397940 + 213091395 + 138244239) %>% # page B-10
#
select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)
##### year 2020
davidson_county_2020 <- nces %>% filter(nces_original_name == "Davidson County" & state == "TN") %>%
mutate(
id = NA,
total_assets = 56699625 + 5853778, #For pensions, they're showing a net asset (it's overfunded) on p. B-140. It looks like there are two plans, so we should add those together:
year = 2020,
total_liabilities = 44660790 + 89820946 + 212989, # sum of above 3 columns
net_opeb_liability = county_opep_liabilities_2020*employee_share,
#OR use page B-143 school professional employees
#net_opeb_liability = 1196937989,
net_pension_liability = -(56699625 + 5853778), #??
expenses = NA,
revenues = NA) %>%
#
select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)
Henrico County schools are a component unit of Henrico County: Henrico-County-VA-FY21-ACFR-Final.pdf. page 25 - 26
henrico_2021 <- nces %>% filter(nces_original_name == "HENRICO CO PBLC SCHS" & state == "VA") %>%
mutate(
id = NA,
total_assets = 384459131,
year = 2021,
total_liabilities = 650708572,
net_opeb_liability = 76925442,
net_pension_liability = 520851160,
expenses = 614221032,
revenues = 268922094) %>% # page 26, line total general revenues
#
select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)
######
henrico_2020 <- nces %>% filter(nces_original_name == "HENRICO CO PBLC SCHS" & state == "VA") %>%
mutate(
id = NA,
total_assets = 393231084,
year = 2020,
total_liabilities = 602815832,
net_opeb_liability = 81756137,
net_pension_liability = 462836320,
expenses = 574231130,
revenues = 211589891) %>% #page 26, line total general revenues
#
select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)
Delaware has school district info in the state ACFR: FY 2021 ACFR (delaware.gov). Balance sheet info is on p. 166-167 and activities are on p. 168-169. –> manually inserted these numbers to excel file. –> Where to find net pension, net OPEB for K12? (State of Delaware in page 20) –> Don’t do this yet. Let me figure out why school districts are only listed in the statistical section–I’m not sure whether the liabilities are consolidated with the state’s.
# readRDS("data/data_from_dbsite_2021.RDS") %>%
# filter(str_detect(name, "State of Delaware")) %>%
# filter(category == "General Purpose")
sd_96_2020 <- top_sd_nces %>%
left_join(top_sd_acfrs) %>% # to get id
left_join(sd2020_db, by = "id") %>% # need to specify join by=id, if not, some rows in sd2020_db loose values (eg. id == 196828)
select(-c(name.y, name.x, state.y)) %>%
rename(state = state.x) %>%
# take out sd whose acfrs are not known to date
filter(!nces_original_name %in% c("Boston", "CHESTERFIELD CO PBLC SCHS", "HENRICO CO PBLC SCHS", "OMAHA PUBLIC SCHOOLS")) %>%
# take out 5 sd of NYC to later rbind with nyc_20_21_5sd
filter(state != "NY") %>%
# Collect data for some sd manually
mutate(year = 2020,
total_liabilities = case_when(nces_original_name == "Davidson County"& state == "TN" ~ 0,
nces_original_name == "Portland SD 1J"& state == "OR" ~ 1882236000,
TRUE ~ total_liabilities), #page 34
net_pension_liability = case_when(nces_original_name == "Davidson County" & state == "TN" ~ 0,
nces_original_name == "Portland SD 1J"& state == "OR" ~ 306140000,
TRUE ~ net_pension_liability), # page 34, Net pension liability-PERS
net_opeb_liability = case_when(nces_original_name == "Davidson County" & state == "TN" ~ 0,
nces_original_name == "Portland SD 1J"& state == "OR" ~ 81319999,
TRUE ~ net_opeb_liability), # page 34, TOTAL OPEB liability-RHIS
expenses = case_when(nces_original_name == "Davidson County" & state == "TN" ~ 0,
nces_original_name == "Portland SD 1J"& state == "OR" ~ 824384000,
TRUE ~ expenses),#page 35
revenues = case_when(nces_original_name == "Davidson County" & state == "TN" ~ 0,
nces_original_name == "Portland SD 1J"& state == "OR" ~ (8271000 + 90072000 + 11368000 + 823400000), #page 35 (charges + operating + grant + Total general revenues)
TRUE ~ revenues))
sd_96_2021 <- top_sd_nces %>%
left_join(top_sd_acfrs) %>% # to get id
left_join(sd2021_db, by = "id") %>% # NOTE: filter(is.na(total_liabilities)) -> some have id carried from 2020, but no values for 2021
select(-c(name.y, name.x, state.y)) %>%
rename(state = state.x) %>%
# take out sd whose acfrs are not known to date
filter(!nces_original_name %in% c("Boston", "CHESTERFIELD CO PBLC SCHS", "HENRICO CO PBLC SCHS", "OMAHA PUBLIC SCHOOLS")) %>%
# take out 5 sd of NYC to later rbind with nyc_20_21_5sd
filter(state != "NY") %>%
mutate(year = 2021,
total_liabilities = case_when(nces_original_name == "Davidson County"& state == "TN" ~ 0,
nces_original_name == "Portland SD 1J"& state == "OR" ~ 2380580000,
TRUE ~ total_liabilities),
net_pension_liability = case_when(nces_original_name == "Davidson County" & state == "TN" ~ 84041613,
nces_original_name == "Portland SD 1J"& state == "OR" ~ 521329000,
TRUE ~ net_pension_liability), # page 32, Net pension liability-PERS
net_opeb_liability = case_when(nces_original_name == "Davidson County" & state == "TN" ~ 1061732808, # page B-93, column School professional employee
nces_original_name == "Portland SD 1J"& state == "OR" ~ 78581000,
TRUE ~ net_opeb_liability), # page 32, TOTAL OPEB liability-RHIS
expenses = case_when(nces_original_name == "Davidson County" & state == "TN" ~ 1195361854,
nces_original_name == "Portland SD 1J"& state == "OR" ~ 931870000,
TRUE ~ expenses), #page 33
revenues = case_when(nces_original_name == "Davidson County" & state == "TN" ~ 488401680,
nces_original_name == "Portland SD 1J"& state == "OR" ~ (1914000 + 113835000 + 1319000 + 822868000), # Geoff: need to take sum 4 of these: charges, operating, capital grant and general revenues)
TRUE ~ revenues) #page 33
)
fairfax_2021 <- nces %>%
filter(nces_original_name == "FAIRFAX CO PBLC SCHS" & state == "VA") %>%
mutate(
id = 200059,
total_assets = 3446600000, #p 10, Summary of Net position
year = 2021,
total_liabilities = 4923543994, #p 21, Statement of Net position
net_opeb_liability = 387461319, #p 21,
net_pension_liability = 4115292996, #p 21
expenses = 3320812299, #p22 Statement of activities
revenues = 76892323 + 411859293 + 194679133 + 2718687903) %>% #p 22,
#
select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)
76892323 + 411859293 + 194679133 + 2718687903
## [1] 3402118652
loudoun_2021 <- nces %>%
filter(nces_original_name == "LOUDOUN CO PBLC SCHS" & state == "VA") %>%
mutate(
id = 197654,
total_assets = 2608200000, #p 11, Summary of Net position
year = 2021,
total_liabilities = 1810600000, #p 11, Summary of Net position
net_opeb_liability = 334800000, #p 17, Outstanding long-term liabilities
net_pension_liability = 1185300000, #p 17, Outstanding long-term liabilities
expenses = 1541300000,
revenues = 1611300000) %>% #p 11, Summary of changes in net position
#
select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)
princewilliam_2021 <- nces %>%
filter(nces_original_name == "PRINCE WILLIAM CO PBLC SCHS" & state == "VA") %>%
mutate(
id = 197665,
total_assets = 2445510537, #p 34, Statement of Net position
year = 2021,
total_liabilities = 1376448983, #p 11, Summary of Net position
net_opeb_liability = 142765589, #p 38, Reconciliation of the Balance Sheet of the Gov funds
net_pension_liability = 1003741236, #p 38, Reconciliation of the Balance Sheet of the Gov funds
expenses = 1376884937, #p 35, statement of activities, line school division
revenues = 5561927 + 280176028 + 127346 + 1268840518) %>% #p35 statement of activities, line school division --> sum of 3 columns program revenues --> 2020 = sum of 3 cols + col total school division X line Total general revenues and transfers?
#
select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)
vabeach_2021 <- nces %>%
filter(nces_original_name == "VA BEACH CITY PBLC SCHS" & state == "VA") %>%
mutate(
id = 197671,
total_assets = 899445665, #p 234, Discretely presented school board - Statement of Net position; column Governmental activities
year = 2021,
total_liabilities = 1077086065, #p 234
net_opeb_liability = 147744317, #p 234
net_pension_liability = 748825120, #p 234
expenses = 881553759, #p 235, statement of activities, line total governmental activities
revenues = 3408114 + 201162593 + 0 + 706048089) %>% #p235, sum of program revenues + total general revenues, statement of activities, line school division --> Similar to 2020: the revenues 985726201 = sum of 4 cols + total general rev?
#
select(ncesID, nces_original_name, county_name, city, state, id, students, year, total_liabilities, net_opeb_liability, net_pension_liability, expenses, revenues)
The majority of SD has total liability to revenues ratio larger than 1 (points below the diagonal line).
top100_result %>%
select(1:2,5, liability_rev_ratio) %>%
arrange(liability_rev_ratio) -> ratio
datatable(ratio)
Increases in most categories.
Palm Beach FL has a steep decrease in net OPEB liability (66% decrease) & a large increase in net pension liability (30% increase)